package com.sq.partner.manager.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.ITradProdInstDao;
import com.sq.partner.manager.mybatis.model.CoreProdInst;
import com.sq.partner.manager.mybatis.model.CoreProdPlan;
import com.sq.partner.manager.mybatis.model.TradCalDef;
import com.sq.partner.manager.mybatis.model.TradItemCal;
import com.sq.partner.manager.mybatis.model.TradProdInst;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;
@Repository
public class TradProdInstDaoImpl extends BaseDaoImpl implements ITradProdInstDao {
	private static Logger logger = LogUtil.getLog(TradProdInstDaoImpl.class);

	

	@Override
	public Page<TradProdInst> queryPage(Page<TradProdInst> page) throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT Trad_Prod_Code as tradProdCode, Trad_Prod_Desc as tradProdDesc, STATUS as status,to_char(START_DATE, 'yyyy-MM-dd HH24:MI:SS')  as startDate ,INSTANT as instant FROM T_TRAD_PROD_INST where 1=1 ");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradProdInst tradProdInst= page.getType();
			if(StrUtil.isNotEmpty(tradProdInst.getTradProdCode())){
				sqlbuf.append(" AND Trad_Prod_Code like ?");
				list.add(tradProdInst.getTradProdCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getTradProdDesc())){
				sqlbuf.append(" AND Trad_Prod_Desc like ?");
				list.add(tradProdInst.getTradProdDesc());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getStatus())){
				sqlbuf.append(" AND STATUS=?");
				list.add(tradProdInst.getStatus());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getVstartDate())){
				sqlbuf.append(" AND START_DATE >= to_date(? ,'yyyy-mm-dd')");
				list.add(tradProdInst.getVstartDate());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getVendDate())){
				sqlbuf.append(" AND START_DATE  < to_date(? ,'yyyy-mm-dd') +1");
				list.add(tradProdInst.getVendDate());
				islike.add(false);
			}
			
			sqlbuf.append(" order by NVL(OPER_TIME,NVL(START_DATE,TO_DATE('1990/01/01', 'yyyy/mm/dd'))) desc,Trad_Prod_Code desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}



	@Override
	public Page<CoreProdInst> queryProdInstPage(Page<CoreProdInst> page)
			throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT inst_code as inst_code, prod_nme as prod_nme  FROM T_CORE_PROD_INST where 1=1");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			CoreProdInst coreProdInst= page.getType();
			if(StrUtil.isNotEmpty(coreProdInst.getProdNme())){
				sqlbuf.append(" AND prod_nme like ?");
				list.add(coreProdInst.getProdNme());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(coreProdInst.getInstCode())){
				sqlbuf.append(" AND inst_code like ?");
				list.add(coreProdInst.getInstCode());
				islike.add(true);
			}
			sqlbuf.append(" order by inst_code desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
		return page;
	}



	@Override
	public void saveTradProdInst(TradProdInst tradProdInst)throws Exception {
		this.add("insertTradProdInst", tradProdInst);
	}
	@Override
	public TradProdInst findMaxTradProdCode(TradProdInst tradProdInst) throws SQLException {
		return  (TradProdInst) this.query("queryMaxTradProdCode", tradProdInst) ;
	}



	@Override
	public void deleteTradProdInst(String tradProdCode) throws Exception {
		this.delete("delTradProdInstById", tradProdCode);
	}



	@Override
	public List<CoreProdPlan> queryProdPlan(String tradProdCode) throws SQLException {
		// TODO Auto-generated method stub
		return this.queryList("queryTradProdPlan", tradProdCode);
	}



	@Override
	public TradProdInst queryTradProdInstById(String tradProdCode)
			throws SQLException {
		return  (TradProdInst)this.query("queryTradProdInstById", tradProdCode);
	}



	@Override
	public Page<CoreProdPlan>  queryProdPlanPage(Page<CoreProdPlan> page,TradProdInst tradProdInst)
			throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT Pla.Prod_Inst_Code as prodinstcode,Pla.PLAN_CODE as planCode,Pla.INST_CODE as instCode,Pla.REQUIRED as required,Pla.INS_TYPE as insType,Ba.INSNME as insnme,nvl(Ba.is_health, 'N') as isHealthFlag "
					+ " FROM T_Core_PROD_PLAN Pla ,T_Core_PLAN_BASE Ba where 1=1  AND Pla.Prod_Inst_Code=?  AND Pla.Plan_Code = Ba.Plan_Code  AND Pla.Inst_Code = Ba.Inst_Code  AND Pla.Plan_Code "
					+ " not in (SELECT ba.plan_code from T_TRAD_PLAN_BASE ba ,T_TRAD_PROD_PLAN pl  WHERE pl.trad_prod_code =? and pl.Trad_plan_code=ba.trad_plan_code ) order by pla.plan_code desc, pla.inst_code desc");
			String sql;
			
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			pstmt.setObject(1, tradProdInst.getProdInstCode());
			pstmt.setObject(2, tradProdInst.getTradProdCode());
			
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			pstmt1.setObject(1, tradProdInst.getProdInstCode());
			pstmt1.setObject(2, tradProdInst.getTradProdCode());
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}



	@Override
	public List<TradCalDef> queryTradCalDef(TradCalDef trad)
			throws SQLException {
		// TODO Auto-generated method stub
		return this.queryList("queryTradCalDefByMap", trad);
	}



	@Override
	public TradProdInst queryTradProdInst(TradProdInst tradProdInst)
			throws SQLException {
		
		return (TradProdInst) this.query("queryTradProdInst", tradProdInst);
	}



	@Override
	public void deleteTradProdInst(TradProdInst tradProdInst) throws Exception {
		this.delete("delTradProdInst", tradProdInst);
	}



	@Override
	public void updateTradProdInst(TradProdInst tradProdInst) throws Exception {
		this.update("updateTradProdInstNotNull", tradProdInst);
	}


	@Override
	public Page<TradProdInst> queryProdPageForInsure(Page<TradProdInst> page)
			throws SQLException {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT Trad_Prod_Code as tradProdCode, Trad_Prod_Desc as tradProdDesc, STATUS as status,to_char(START_DATE, 'yyyy-MM-dd HH24:MI:SS')  as startDate ,INSTANT as instant,POOL_TYPE as poolType,trad_app_type as tradAppType,AGENT_TYPE as agentType FROM T_TRAD_PROD_INST where 1=1 and status = 1");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			TradProdInst tradProdInst= page.getType();
			if(StrUtil.isNotEmpty(tradProdInst.getTradProdCode())){
				sqlbuf.append(" AND Trad_Prod_Code like ?");
				list.add(tradProdInst.getTradProdCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getTradProdDesc())){
				sqlbuf.append(" AND Trad_Prod_Desc like ?");
				list.add(tradProdInst.getTradProdDesc());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getStatus())){
				sqlbuf.append(" AND STATUS=?");
				list.add(tradProdInst.getStatus());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getVstartDate())){
				sqlbuf.append(" AND START_DATE >= to_date(? ,'yyyy-mm-dd')");
				list.add(tradProdInst.getVstartDate());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(tradProdInst.getVendDate())){
				sqlbuf.append(" AND START_DATE <= to_date(? ,'yyyy-mm-dd') + 1");
				list.add(tradProdInst.getVendDate());
				islike.add(false);
			}
			
			sqlbuf.append(" order by Start_Date desc,Trad_Prod_Desc desc");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);

			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}



	@SuppressWarnings("unchecked")
	@Override
	public List<TradItemCal> queryTradItemCal(TradItemCal trad)
			throws SQLException {
		return this.queryList("queryTradItemCalByMap", trad);
	}



	@Override
	public void updateTradProdInstIsHealthFlag(TradProdInst tradProdInst) throws Exception {
		this.update("updateTradProdInstIsHealthFlag", tradProdInst);
	}

}
